package com.zhufei.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.News;

public class NewsDao extends DBCon{
	//public static Connection con = null;
	private static String TABLE = "zhufei_news";
	private static String ID = "ID";
	private static String IS_TOP = "is_top";
	private static String CONTENT = "content";
	private static String TITLE = "title";
	private static String DATETIME = "datetime";
	
	/**
	 * 修改新闻
	 * @param ID      新闻ID
	 * @param title   新闻标题
	 * @param content 新闻内容
	 * @return  true表示成功，false表示失败
	 */
	public static boolean updateNews(String ID, String title, String content){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set title='"+title+"',content='"+content+"' where ID ='"+ID+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 修改新闻(此函数增加了置顶参数)
	 * @param ID      新闻ID
	 * @param title   新闻标题
	 * @param content 新闻内容
	 * @param is_top  置顶参数
	 * @return  true表示成功，false表示失败
	 */
	public static boolean updateNews(String ID, String title, String content, int is_top){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set title='"+title+"',content='"+content+"',is_top="+is_top+" where ID ='"+ID+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 设置新闻的置顶与否
	 * @param ID  新闻ID
	 * @param is_top  参数，1表示置顶，0表示不置顶
	 * @return
	 */
	public static boolean setTopNews(String ID, int is_top){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set "+IS_TOP+"="+is_top+" where ID ='"+ID+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 删除
	 * @param ID      新闻ID
	 * @return  true表示成功，false表示失败
	 */
	public static boolean deleteNews(String ID){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "delete from "+TABLE+" where ID ='"+ID+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的新闻对象
	 * @param index  从第index条开始获取（第一条新闻下标是0）
	 * @param number  获取新闻的数量，输入-1则获取数据库中所有新闻
	 * @return  新闻对象的ArrayList
	 */
	public static ArrayList<News> getNewses(int index, int number){
		ArrayList<News> newsList = new ArrayList<News>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			if(number != -1){
				sql = "select * from "+ TABLE +" order by datetime desc limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE +" order by datetime desc";
			}
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				News news = new News();
				news.setID(rs.getString("ID"));
				news.setContent(rs.getString("content"));
				news.setTitle(rs.getString("title"));
				news.setDate(rs.getString("datetime"));
				news.setIs_top(rs.getInt(IS_TOP));
				newsList.add(news);
			}
			rs.close();
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的新闻对象(此函数增加了置顶参数)
	 * @param index   从第index条开始获取（第一条新闻下标是0）
	 * @param number  获取新闻的数量，输入-1则获取数据库中所有新闻
	 * @param is_top  1表示返回置顶的新闻，0表示返回不置顶的新闻，-1表示不管是否置顶都返回
	 * @return  新闻对象的ArrayList
	 */
	public static ArrayList<News> getNewses(int index, int number, int is_top){
		ArrayList<News> newsList = new ArrayList<News>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			String where = "";
			if(is_top == 1){
				where = " where "+IS_TOP+"=1 ";
			}else if(is_top == -1){
				where = " where "+IS_TOP+"=0 ";
			}
			if(number != -1){
				sql = "select * from "+ TABLE +where+" order by datetime desc limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE +where+" order by datetime desc";
			}
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				News news = new News();
				news.setID(rs.getString("ID"));
				news.setContent(rs.getString("content"));
				news.setTitle(rs.getString("title"));
				news.setDate(rs.getString("datetime"));
				news.setIs_top(rs.getInt(IS_TOP));
				newsList.add(news);
			}
			rs.close();
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 根据id获取新闻对象
	 * @param id 新闻id
	 * @return 如果没有记录返回null
	 */
	public static News getNews(String id){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			sql = "select * from "+ TABLE +" where "+ID+"='"+id+"'";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				News news = new News();
				news.setID(rs.getString("ID"));
				news.setContent(rs.getString("content"));
				news.setTitle(rs.getString("title"));
				news.setDate(rs.getString("datetime"));
				news.setIs_top(rs.getInt(IS_TOP));
				rs.close();
				return news;
			}
			rs.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 根据新闻id返回其在数据库中的index
	 * @param id  新闻id
	 * @return  index从0开始
	 */
	public static int getIndex(String id){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			sql = "select ID from "+ TABLE +" order by datetime desc";
			
			ResultSet rs = statement.executeQuery(sql);
			int index = 0;
			while(rs.next()){
				if(rs.getString(ID).equals(id)){
					return index;
				}
				index++;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return -1;
	}
	
	/**
	 * 插入一条新闻到数据库，插入时间自动生成
	 * @param title    新闻标题
	 * @param content  新闻内容
	 * @return    返回是否插入成功
	 */
	public static boolean insertNews(String title, String content){
		DBCon con = new DBCon();
		try {
			String ID = GlobalsDao.getUUID();
			String dt = GlobalsDao.getCurrentDateTime();
			Statement statement = con.getStatement();
			String sql = "insert into " + TABLE + " (ID,title,content,datetime) values ('"+ID+"','"+title+"','"+content+"','"+dt+"')";
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 插入一条新闻到数据库，插入时间自动生成(此函数多一个置顶参数)
	 * @param title    新闻标题
	 * @param content  新闻内容
	 * @param is_top   是否置顶，1表示置顶，0表示不置顶
	 * @return    返回是否插入成功
	 */
	public static boolean insertNews(String title, String content, int is_top){
		DBCon con = new DBCon();
		try {
			String ID = GlobalsDao.getUUID();
			String dt = GlobalsDao.getCurrentDateTime();
			Statement statement = con.getStatement();
			String sql = "insert into " + TABLE + " (ID,title,content,datetime,is_top) values ('"+ID+"','"+title+"','"+content+"','"+dt+"',"+is_top+")";
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 获取数据库中新闻数量总数
	 * @return  返回数据库有多少条新闻
	 */
	public static int getCountOfNews(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE;
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				rs.close();
				return count;
			}
			else {
				rs.close();
				return 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 获取数据库中置顶新闻数量总数
	 * @return  返回数据库有多少条置顶新闻
	 */
	public static int getCountOfTopNews(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE+" where is_top=1";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				rs.close();
				return count;
			}
			else {
				rs.close();
				return 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 获取链接对象
	 */
	private static void getConnection_(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection
			( GlobalsDao.database_url, GlobalsDao.database,GlobalsDao.database_password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
